﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using UserClassLIB;
using UtilityClassLIB;

namespace UserServiceLibrary
{
    
    public class UserService : IUserService
    {

        UtilityClass utility_obj = new UtilityClass();//creates utility class object
        String connectionString = UtilityClass.ConnectionString();//gets connection string from utility class
             
        public List<UserClass> GetAllUser()
        {
            List<UserClass> userList = new List<UserClass>();
            
   
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string queryString = "SELECT userID,firstName, lastName, empID, userName, pswrd FROM [dbo].[tbUser]";
                try
                {

                    connection.Open();

                    SqlCommand command = new SqlCommand(queryString, connection);

                    SqlDataReader reader = command.ExecuteReader();
                                        
                        while (reader.Read())
                        {
                            UserClass aUser = new UserClass();
                            
                            aUser.UserID = reader.GetInt32(0);                            
                            aUser.FirstName = reader.GetString(1);
                            aUser.LastName = reader.GetString(2);
                            aUser.EmployeeID = reader.GetInt32(3);
                            aUser.Username = reader.GetString(4);
                            aUser.Password = reader.GetString(5);
                         
                                                             
                            userList.Add(aUser);
                        }
                        connection.Close();

                }

                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

                return userList;
            }
        }        
                   
        public bool AddUser(UserClass obj)
            {
                bool success = false;

                                   
                try
                {

                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                       
                        string sqlString = "insert into [dbo].[tbUser](firstName, lastName, empID, userName, pswrd, userID) values(@firstName, @lastName, @empID, @userName, @pswrd, @userID)";
                        conn.Open();
                        using (SqlCommand command = new SqlCommand(sqlString, conn))
                        {
                            command.Parameters.AddWithValue("@firstName", obj.FirstName);
                            command.Parameters.AddWithValue("@lastName", obj.LastName);
                            command.Parameters.AddWithValue("@username", obj.Username);
                            command.Parameters.AddWithValue("@pswrd", obj.Password);
                            command.Parameters.AddWithValue("@empID", obj.EmployeeID);
                            command.Parameters.AddWithValue("@userID", obj.UserID);

                            int rows = command.ExecuteNonQuery();

                            if (rows == 1)
                            {
                                success = true;
                            }

                            //rows number of record got updated
                        }
                    }
                }
                catch (SqlException ex)
                {
                    //Log exception
                    //Display Error message
                    Console.WriteLine(ex.ToString());
                }

                return success;
            }           
        
        public bool UpdateUser(UserClass obj)
        
        {
           
            try
            {
                                   
                using (SqlConnection conn =  new SqlConnection(connectionString))
                {
                    string sqlString = "UPDATE [dbo].[tbUser] SET FirstName=@firstName, LastName=@lastName, EmployeeId=@empId, UserName=@userName, Password=@pswrd WHERE UserID=@userID";
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sqlString, conn))                    
                    {                                           
                        command.Parameters.AddWithValue("@firstName", obj.FirstName);
                        command.Parameters.AddWithValue("@lastName", obj.LastName);
                        command.Parameters.AddWithValue("@userName", obj.Username );
                        command.Parameters.AddWithValue("@pswrd", obj.Password);
                        command.Parameters.AddWithValue("@empID", obj.EmployeeID);
                        command.Parameters.AddWithValue("@userID", obj.UserID);  
        
                        int rows = command.ExecuteNonQuery();
                        return true;
                        //rows number of record got updated
                    }
                }
            }
            catch (SqlException ex)
            {
                //Log exception
                //Display Error message
                Console.WriteLine(ex.ToString());
            }
                
                return true;
        }

        public bool DeleteUser(UserClass obj)
        {
            try
            {
                string sqlString = "DELETE FROM [dbo].[tbUser] WHERE UserID=@userID";

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(sqlString, conn))
                    {
                        command.Parameters.AddWithValue("@userID", obj.UserID);

                        int rows = command.ExecuteNonQuery();
                        return true;
                        //rows number of record got deleted
                    }
                }
            }
            catch (SqlException ex)
            {
                //Log exception
                //Display Error message
                Console.WriteLine(ex.ToString());
            }

            return true;

        }
              
    }
    
}
